﻿using Npgsql;
using Ortodoctor.Componentes;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace Ortodoctor.modelo
{
    public class modeloCaixa : conexao
    {
        public modeloCaixa()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public int insert(Caixas c)
        {
            int id = 0;
            try
            {
                //con.open();
                String sql = @"INSERT INTO caixas(tipo, nome, id_clinica) VALUES (:p1, :p2, :p3) returning id_caixas";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", c.Tipo);
                com.Parameters.AddWithValue("p2", c.Nome);
                com.Parameters.AddWithValue("p3", HttpContext.Current.Session["ID_CLINICA"]);
                id = (int.Parse(com.ExecuteScalar().ToString()));
            }
            finally
            {
                conn.Close();
            }
            return id;
        }

        public void Log(string operacao, string log)
        {
            DateTime dt = DateTime.Now;
            try
            {
                String sql = @"INSERT INTO caixas_log(operacao, log, data) values (:p1, :p2, :p3)";

                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", operacao);
                com.Parameters.AddWithValue("p2", log);
                com.Parameters.AddWithValue("p3", DateTime.Now.ToShortDateString() + " - " + DateTime.Now.ToShortTimeString());
                com.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
        }

        public int insert(Caixas c, int id_clinica)
        {
            int id = 0;
            try
            {
                //con.open();
                String sql = @"INSERT INTO caixas(tipo, nome, id_clinica) VALUES (:p1, :p2, :p3) returning id_caixas";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", c.Tipo);
                com.Parameters.AddWithValue("p2", c.Nome);
                com.Parameters.AddWithValue("p3", id_clinica);
                id = (int.Parse(com.ExecuteScalar().ToString()));
            }
            finally
            {
                conn.Close();
            }
            return id;
        }

        internal DataTable getAll()
        {
            DataTable dt = new DataTable();
            try
            {
                String sql = @"select c.*, en.cidade as cidade from caixas as c inner join 
                clinica using(id_clinica) inner join endereco as en using(id_endereco)";
                if (HttpContext.Current.Session["USUARIO_ACESSO"].ToString().CompareTo("2") != 0)
                {
                    //                    sql = @"
                    //                        select id_cliente, data, cliente.nome as nome_cliente, funcionario.nome as nome_dentista from comparecimento 
                    //                        inner join cliente using(id_cliente) inner join dentista using (id_dentista)
                    //                        inner join funcionario using(id_funcionario) where dentista.id_clinica = 
                    //                        " + id_clinica + " and data = '" + data + "' and cliente.id_clinica=" + HttpContext.Current.Session["ID_CLINICA"];
                    sql = @"select c.*, en.cidade as cidade from caixas as c inner join 
                    clinica using(id_clinica) inner join endereco as en using(id_endereco) where 
                    c.id_clinica=" + HttpContext.Current.Session["ID_CLINICA"];
                }
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public List<CaixaValor> getAllValor(int id_caixas, string data, bool todas)
        {
            DataTable dt = new DataTable();
            List<CaixaValor> cx = new List<CaixaValor>();
            try
            {
                String sql = @"select cv.*, u.login from caixas inner join caixa_valor as cv using (id_caixas) 
                    inner join usuario as u using (id_usuario) where id_caixas = :id";

                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id_caixas);

                if (!todas)
                {
                    string dataInv = data.Split('/')[2]+"-"+data.Split('/')[1]+"-"+data.Split('/')[0];
                    sql = @"select cv.*, u.login from caixas inner join caixa_valor as cv using (id_caixas) 
                    inner join usuario as u using (id_usuario) where id_caixas = :id and 
                    dia = :dia";
                    com = new NpgsqlCommand(sql, conn);
                    com.Parameters.AddWithValue("id", id_caixas);
                    com.Parameters.AddWithValue("dia", dataInv);
                }
                
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
                for (int i = 0; i < dt.Rows.Count; ++i)
                {
                    CaixaValor cv = new CaixaValor();
                    cv.Dia = Convert.ToDateTime(dt.Rows[i]["dia"].ToString());
                    cv.Valor = Convert.ToDouble(dt.Rows[i]["valor"].ToString());
                    cv.IdUsuario = Convert.ToInt32(dt.Rows[i]["id_usuario"].ToString());
                    cv.LoginUsuario = dt.Rows[i]["id_usuario"].ToString();
                    cv.Observacoes = dt.Rows[i]["observacoes"].ToString();
                    cx.Add(cv);
                }
            }
            finally
            {
                conn.Close();
            }
            return cx;
        }

        public Caixas get(int id)
        {
            DataTable dt = new DataTable();
            Caixas c;
            try
            {
                String sql = @"select c.*, e.cidade as cidade from caixas as c inner join clinica using (id_clinica)
                inner join endereco as e using (id_endereco) where c.id_caixas = :id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
                c = new Caixas(Convert.ToInt32(dt.Rows[0]["id_caixas"].ToString()), dt.Rows[0]["tipo"].ToString(), dt.Rows[0]["nome"].ToString(), dt.Rows[0]["atualizacao"].ToString());
                c.IdClinica = Convert.ToInt32(dt.Rows[0]["id_clinica"].ToString());
                c.CidadeClinica = dt.Rows[0]["cidade"].ToString(); 
            }
            finally
            {
                conn.Close();
            }
            return c;
        }

        public void addEntrada(int id_caixas, double valor, String obs)
        {
            try
            {
                String sql = @"INSERT INTO caixa_valor(id_caixas, valor, id_usuario, observacoes) 
                            VALUES (:p1, :p2, :p3, :p4);";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", id_caixas);
                com.Parameters.AddWithValue("p2", valor);
                com.Parameters.AddWithValue("p3", HttpContext.Current.Session["ID_USUARIO_ACESSO"]);
                com.Parameters.AddWithValue("p4", obs);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                DataTable dt = new DataTable();
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
        }

        public void delete(int id_caixa)
        {
            try
            {
                //con.open();
                String sql = "DELETE FROM caixas WHERE id_caixas = " + id_caixa;
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.ExecuteNonQuery();

                sql = "update caixas_hist set id_usuario_modificacao=" + HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString() + 
                      ", data_modificacao = '" + DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss") + "'" +
                      " WHERE id_caixas = " + id_caixa;
                com = new NpgsqlCommand(sql, conn);
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public double getSomaDia(int id_caixas, string dia)
        {
            DataTable dt = new DataTable();
            double soma_dia=0;
            string dataInv = dia.Split('/')[2]+"-"+dia.Split('/')[1]+"-"+dia.Split('/')[0];
            try
            {
                String sql = "select sum(valor) as somaDia from caixa_valor where dia=:p1 and id_caixas=:p2";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", dataInv);
                com.Parameters.AddWithValue("p2", id_caixas);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                try
                {
                    da.Fill(dt);
                    try
                    {
                        soma_dia = Convert.ToDouble(dt.Rows[0]["somaDia"].ToString());
                    }
                    catch (Exception) { soma_dia = 0; }
                }
                catch (Exception) { }
            }
            finally
            {
                conn.Close();
            }
            return soma_dia;
        }
        
        public bool editar(int id, string tipo, string nome)
        {
            DataTable dt = new DataTable();
            String sql = "update caixas set tipo= :p1 , nome= :p2, id_usuario_modificacao = :id_usuario where id_caixas = :p3";
            NpgsqlCommand com = new NpgsqlCommand(sql, conn);
            com.Parameters.AddWithValue("p1", tipo);
            com.Parameters.AddWithValue("p2", nome);
            com.Parameters.AddWithValue("p3", id);
            com.Parameters.AddWithValue("id_usuario", HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
            try
            {
                com.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                return false;
            }
            return true;

        }

        public double getSomaTotal(int id_caixas)
        {
            DataTable dt = new DataTable();
            double soma_total = 0;
            try
            {
                String sql = "select sum(valor) as somaTotal from caixa_valor where id_caixas=:p1";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", id_caixas);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
                try
                {
                    soma_total = Convert.ToDouble(dt.Rows[0]["somaTotal"].ToString());
                }
                catch (Exception) { soma_total = 0; }
            }
            finally
            {
                conn.Close();
            }
            return soma_total;
        }

        public DataTable getParcelasCaixa(string datainicial, string datafinal)
        {
            DataTable dt = new DataTable();
            DataTable dtemp = new DataTable();
            try
            {
                string sql = string.Format(@"
                                SELECT CLIENTE.NOME AS ""Cliente"", 
                                       PROCEDIMENTO.NOME_PROCEDIMENTO AS ""Procedimento"", 
                                       'R$' || CAST(CAST(PARCELAS.VALOR AS NUMERIC(12,2)) AS VARCHAR) AS ""Valor"",
                                       PAGAMENTO_PARCELA.DATA AS ""Data""
                                FROM PARCELAS INNER JOIN PAGAMENTO_PARCELA USING(ID_PARCELA)
		                                           INNER JOIN PROCEDIMENTO_CLIENTE USING(ID_PROCEDIMENTO_CLIENTE)
		                                           INNER JOIN PROCEDIMENTO USING(ID_PROCEDIMENTO)
		                                           INNER JOIN CLIENTE USING (ID_CLIENTE)
                                WHERE DATA BETWEEN '{0}' AND '{1}'",
                                            Convert.ToDateTime(datainicial).ToString("MM/dd/yyyy"),
                                            Convert.ToDateTime(datafinal).ToString("MM/dd/yyyy"));
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);

                foreach (DataColumn dc in dt.Columns)
                    dtemp.Columns.Add(dc.ColumnName, typeof(string));

                foreach (DataRow dr in dt.Rows)
                {
                    DataRow drow = dtemp.NewRow();
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dc.ColumnName.ToUpper().Equals("DATA"))
                            drow[dc.ColumnName] = Convert.ToDateTime(dr[dc.ColumnName]).ToString("dd/MM/yyyy");
                        else
                            drow[dc.ColumnName] = dr[dc.ColumnName].ToString();
                    }
                    dtemp.Rows.Add(drow);
                }
            }
            finally
            {
                conn.Close();
            }
            return dtemp;
        }

        public DataTable getCaixaConta(string datainicial, string datafinal, int clinica)
        {
            DataTable dt = new DataTable();
            DataTable dtemp = new DataTable();
            try
            {
                string sql = string.Format(@"
                                SELECT NOME AS ""Caixa"", 
                                       TIPO AS ""Tipo"",
                                       DIA AS ""Data"",
                                       'R$' || CAST(CAST(VALOR AS NUMERIC(12,2)) AS VARCHAR) AS ""Valor"",
                                       ENDERECO.CIDADE as ""Clinica""
                                FROM CAIXAS INNER JOIN CAIXA_VALOR USING(ID_CAIXAS)
	                                    INNER JOIN CLINICA USING(ID_CLINICA)
	                                    INNER JOIN ENDERECO USING(ID_ENDERECO)
                                WHERE DIA BETWEEN '{0}' AND '{1}' AND (ID_CLINICA = {2} OR 99 = {2})",
                                            Convert.ToDateTime(datainicial).ToString("MM/dd/yyyy"),
                                            Convert.ToDateTime(datafinal).ToString("MM/dd/yyyy"),
                                            clinica);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);


                foreach (DataColumn dc in dt.Columns)
                    dtemp.Columns.Add(dc.ColumnName, typeof(string));

                foreach (DataRow dr in dt.Rows)
                {
                    DataRow drow = dtemp.NewRow();
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dc.ColumnName.ToUpper().Equals("DATA"))
                            drow[dc.ColumnName] = Convert.ToDateTime(dr[dc.ColumnName]).ToString("dd/MM/yyyy");
                        else
                            drow[dc.ColumnName] = dr[dc.ColumnName].ToString();
                    }
                    dtemp.Rows.Add(drow);
                }
            }
            finally
            {
                conn.Close();
            }
            return dtemp;
        }
    }
}